﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlClient;

namespace {namespace}.DataAccess
{
    public class SqlConnector : IDisposable
    {
        
        private SqlConnection _connection = null;
        private SqlDataAdapter _dataAdapter = new SqlDataAdapter();
        private SqlCommand _command = new SqlCommand();
        public SqlConnector()
        {
            InitConnection();
            _connection.Open();
            
            _command = _connection.CreateCommand();
            _dataAdapter = new SqlDataAdapter(_command);
        }

        
        
        private void InitConnection()
        {
            try
            {
                string cnString = ConfigurationManager.AppSettings["ConnectionString"];
                if(String.IsNullOrEmpty(cnString))
                    throw new ConfigurationErrorsException("ConnectionString key could not be found on configuration file or configuration file not found.");
                _connection = new SqlConnection(cnString);    
                _connection.Open();
                _command.Connection = _connection;
            }

            catch (Exception ex)
            {
                throw new TypeInitializationException(this.GetType().FullName, ex);
            }
        }


        public DataTable GetDaTaTable(string query)
        {
            DataTable res = new DataTable();
            _command.CommandType = CommandType.Text;
            _command.CommandText = query;
            _dataAdapter.Fill(res);
            return res;
        }
        
        public DataTable GetDaTaTable(string query, int startIndex, int EndIndex)
        {
            DataTable res = new DataTable();
            _command.CommandType = CommandType.Text;
            _command.CommandText = query;
            _dataAdapter.Fill(startIndex, EndIndex, res);
            return res;
        }

        public void ExecuteNonQuery(string query)
        {
            _command.CommandType = CommandType.Text;
            _command.CommandText = query;
            _command.ExecuteNonQuery();
        }
        
        public void ExecuteNonQuery(SqlCommand command)
        {
            _command = command;
            _command.Connection = _connection;
            _command.ExecuteNonQuery();
        }
        
		public void ExecuteNonQuery(string query, DbParameter[] Parameters)
        {
			_command.Parameters.Clear();
            _command.CommandType = CommandType.Text;
            _command.CommandText = query;
            foreach(DbParameter param in Parameters)
            {
				SqlParameter p = new SqlParameter(param.ParameterName, param.DbType);
				p.Value = param.Value;
				_command.Parameters.Add(p);
            }
            _command.ExecuteNonQuery();
        }
        
        public DbParameter CreateParameter()
        {
            return _command.CreateParameter();
        }
        
		public Int64 GetTableMaxColumnValue(string TableName, string ColumnName)
        {
            //_command = new SqlCommand();
            //_command.Transaction = transaction;
            _command.Parameters.Clear();
            _command.CommandText = "SELECT max([" + ColumnName + "]) from [" + TableName + "]";//_command.CommandText = "SELECT last_insert_rowid()";
            return (Int64)_command.ExecuteScalar();
        }

        public void Dispose()
        {
            try
            {

            if(_connection.State != ConnectionState.Closed)
                _connection.Close();

            }
            catch (Exception ex) 
            {
                
            }
            _connection.Dispose();
            _command.Dispose();
            _dataAdapter.Dispose();
        }
    }
}
